<?php

/**
 * Subclass for performing query and update operations on the 'ringtone_download' table.
 *
 * 
 *
 * @package lib.model
 */ 
class RingtoneDownloadPeer extends BaseRingtoneDownloadPeer
{
    static function getDailyRingtoneDowload($type,$sourceType,$startDate,$endDate,$price = 3000, $category = null){
        $con = new MysqlDAL('propel');
        $sql =" SELECT
                    sum(amount) as totalDownload,
                    sum(amount*3000) as totalMoney,
                    date(download_datetime) as dateTime
                From ringtone_download";
        if ($category){
            $sql .=" LEFT JOIN ringtone on ringtone_download.ringtone_id=ringtone.id";
        }
        
        $sql .= " WHERE 1 = 1";
        if($type == 1){
            $sql .=" AND type = 1";
        }elseif($type == 2){
            $sql .=" AND type = 0";
        }
        if($sourceType == 2){
            $sql .=" AND source_type = 'CRBT'";
        }elseif($sourceType == 1){
            $sql .=" AND source_type <> 'CRBT'";
        }
        if ($startDate){
            $sql .=" AND date(download_datetime)  >= '".$startDate."'";
        }
        if ($category){
            $sql .=" AND ringtone.category_id  =".$category;
        }
      	if ($startDate){
            $sql .=" AND date(download_datetime)  <= '".$endDate."'";
        }
        $sql .=" AND price = $price
                Group by date(download_datetime)
                Order by ringtone_download.download_datetime desc
                ";        
        return $con->doSelect($sql);            
    }
    
    static function getMonthlyRingtoneDowload($type,$sourceType,$price = 3000){
        $con = new MysqlDAL('propel');
        $sql =" SELECT
                    sum(amount) as totalDownload,
                    sum(amount*3000) as totalMoney,
                    year(download_datetime) as year,
                    month(download_datetime) as month
                From ringtone_download
                WHERE 1 = 1
                ";
        if($type == 1){
            $sql .=" AND type = 1";
        }elseif($type == 2){
            $sql .=" AND type = 0";
        }
        if($sourceType == 2){
            $sql .=" AND source_type = 'CRBT'";
        }elseif($sourceType == 1){
            $sql .=" AND source_type <> 'CRBT'";
        }
        $sql .=" AND price = $price
                Group by YEAR(ringtone_download.download_datetime), MONTH(ringtone_download.download_datetime)
                Order by ringtone_download.download_datetime desc
                ";        
        return $con->doSelect($sql);            
    }
    
    public static function getDownload($phoneNumber,$downloadSource,$createdDatetimeFrom,$createdDatetimeTo,$limit,$offset)
    {
    	$con = new MysqlDAL('propel');
        $sql = "SELECT rd.id,
                       rd.ringtone_id,
                       rd.user_id,
                       rt.name,
                       rd.from_phone,
                       rd.to_phone,
                       rd.price,
                       rd.amount,
                       rd.download_datetime,
                       rd.type,
                       rd.source_type,
                       rd.message,
                       rd.sms_count
               FROM ringtone_download AS rd
               INNER JOIN ringtone AS rt
               ON rd.ringtone_id = rt.id
               WHERE rd.type = 1
               ";
        if($phoneNumber != "" || $createdDatetimeFrom != "" || $createdDatetimeTo != ""){
            $sql.= " AND rd.id > 0";
        }
        if($phoneNumber != ""){
            $sql.= " AND rd.from_phone = ".$phoneNumber;
        }
        if(($downloadSource != "") && ($downloadSource != "All"))
        {
        	$sql.= " AND rd.source_type = '".$downloadSource."'";
        }
        else {
        	$sql.= " AND rd.source_type IN ('WAP','WEB','SMS')" ;
        }
        if($createdDatetimeFrom != ""){
            $sql.= " AND UNIX_TIMESTAMP(rd.download_datetime) > ".$createdDatetimeFrom;
        }
        if($createdDatetimeTo != ""){
            $sql.= " AND UNIX_TIMESTAMP(rd.download_datetime) < ".$createdDatetimeTo;
        }
        $sql.= " ORDER BY rd.download_datetime DESC ";
        if($limit){
            $sql.=" LIMIT ".$limit;
        }
        if($offset){
            $sql.=" OFFSET ".$offset;
        }
        return $con->doSelect($sql);
    }
    
public static function getTotalDownload($phoneNumber,$downloadSource,$createdDatetimeFrom,$createdDatetimeTo,$limit,$offset)
    {
    	$con = new MysqlDAL('propel');
        $sql = "SELECT count(rd.id) AS total
               FROM ringtone_download AS rd
               INNER JOIN ringtone AS rt
               ON rd.ringtone_id = rt.id
               WHERE rd.type = 1
               ";
        if($phoneNumber != "" || $createdDatetimeFrom != "" || $createdDatetimeTo != ""){
            $sql.= " AND rd.id > 0";
        }
        if($phoneNumber != ""){
            $sql.= " AND rd.from_phone = ".$phoneNumber;
        }
        if(($downloadSource != "") && ($downloadSource != "All"))
        {
        	$sql.= " AND rd.source_type = '".$downloadSource."'";
        }
    	else {
        	$sql.= " AND rd.source_type IN ('WAP','WEB','SMS')" ;
        }
        if($createdDatetimeFrom != ""){
            $sql.= " AND UNIX_TIMESTAMP(rd.download_datetime) > ".$createdDatetimeFrom;
        }
        if($createdDatetimeTo != ""){
            $sql.= " AND UNIX_TIMESTAMP(rd.download_datetime) < ".$createdDatetimeTo;
        }
        $totalDownload = $con->doSelect($sql);
        if($totalDownload){
            $totalDownload = $totalDownload[0]['total'];
        }else{
            $totalDownload = 0;
        }
        return $totalDownload;
    }
    
    //Tăng sms_count lên 1, return sms_count hiện thời
    public static function updateSmsCount($id)
    {
    	$con = new MysqlDAL('propel');
    	$sql = " UPDATE ringtone_download AS rd
    			 SET rd.sms_count = rd.sms_count + 1
    			 WHERE rd.id = {$id}";
    	$sql2 = "SELECT rd.sms_count AS sms_count
    			 FROM ringtone_download AS rd
    			 WHERE rd.id = {$id}";
    	$con->doUpdate($sql);
    	$sms_count = $con->doSelect($sql2);
    	return $sms_count[0]['sms_count'];
    }
}
